﻿// Daydlg.cpp : 实现文件
//

#include "stdafx.h"
#include "backstage.h"
#include "Daydlg.h"
#include "afxdialogex.h"

#include "ADOConn.h"

// Daydlg 对话框

IMPLEMENT_DYNAMIC(Daydlg, CDialogEx)

Daydlg::Daydlg(CWnd* pParent /*=NULL*/)
	: CDialogEx(Daydlg::IDD, pParent)
	, m_Money(_T(""))
	, m_YMoney(_T(""))
{

}

Daydlg::~Daydlg()
{
}

void Daydlg::DoDataExchange(CDataExchange* pDX)
{
	CDialogEx::DoDataExchange(pDX);
	DDX_Control(pDX, IDC_LIST3, m_Grid);
	//  DDX_Text(pDX, IDC_EDIT1, m_Money);
	DDX_Control(pDX, IDC_DATETIMEPICKER1, m_Day);
	DDX_Text(pDX, IDC_EDIT1, m_Money);
	DDX_Text(pDX, IDC_EDIT2, m_YMoney);
}


BEGIN_MESSAGE_MAP(Daydlg, CDialogEx)
	ON_BN_CLICKED(IDC_BUTQUERY, &Daydlg::OnBnClickedButquery)
END_MESSAGE_MAP()


// Daydlg 消息处理程序


BOOL Daydlg::OnInitDialog(void)
{
	CDialog::OnInitDialog();
	//设置列表视图的扩展风格
	m_Grid.SetExtendedStyle(LVS_EX_FLATSB //扁平风格显示滚动条
		|LVS_EX_FULLROWSELECT //允许整行选中
		|LVS_EX_HEADERDRAGDROP //允许整列拖动
		|LVS_EX_ONECLICKACTIVATE //单击选中项
		|LVS_EX_GRIDLINES); //画出网格线
	//设置表头
	m_Grid.InsertColumn(0,_T("商品编号"),LVCFMT_LEFT,70,0); //设置商品编号列
	m_Grid.InsertColumn(1,_T("商品名称"),LVCFMT_LEFT,200,1); //设置商品名称列
	m_Grid.InsertColumn(2,_T("商品单价"),LVCFMT_LEFT,70,2); //设置商品单价列
	m_Grid.InsertColumn(3,_T("商品进价"),LVCFMT_LEFT,70,3); //设置商品进价列
	m_Grid.InsertColumn(4,_T("销售数量"),LVCFMT_LEFT,70,4); //设置销售数量列

	CTime time = CTime::GetCurrentTime(); //获得系统当前时间
	m_Day.SetTime(&time); //设置时间控件显示系统当前日期

	return TRUE; 
}


void Daydlg::OnBnClickedButquery()
{
	// TODO: 在此添加控件通知处理程序代码
	m_Grid.DeleteAllItems(); //清空列表视图控件中的数据

	CString sql,m_Xsph;
	CString spbh[1000],spmc[1000];
	double spdj[1000],spjj[1000];

	CTime time;
	m_Day.GetTime(time); //获得时间控件中的日期

	//设置SQL查询语句，去除商品编号重复的记录
	sql.Format(_T("select distinct xs.SPBH,SPMC,XSJG,JHJG \
		from xs inner join sp on xs.spbh = sp.spbh where sxsj = '%s'"),time.Format("%Y-%m-%d"));

	ADOConn m_AdoConn; //ADOConn对象
	m_AdoConn.OnInitADOConn(); //连接数据库
	m_AdoConn.m_pRecordset = m_AdoConn.GetRecordSet((_bstr_t)sql); //根据SQL语句查询

	int num=0;
	while(!m_AdoConn.m_pRecordset->adoEOF)
	{
		spbh[num] = (char*)(_bstr_t)m_AdoConn.m_pRecordset->GetCollect("SPBH"); //获得所有商品编号
		m_Grid.InsertItem(num,_T("")); //向列表视图控件中插入行
		m_Grid.SetItemText(num,0,spbh[num]); //向列表视图控件中插入商品编号

		spmc[num] = (char*)(_bstr_t)m_AdoConn.m_pRecordset->GetCollect("SPMC"); 
		m_Grid.SetItemText(num,1,spmc[num]); //向列表视图控件中插入商品名称

		spdj[num] = m_AdoConn.m_pRecordset->GetCollect("XSJG"); 
		m_Grid.SetItemText(num,2,(_bstr_t)spdj[num]); //插入商品单价

		spjj[num] = m_AdoConn.m_pRecordset->GetCollect("JHJG"); 
		m_Grid.SetItemText(num,3,(_bstr_t)spjj[num]); //插入商品单价

		m_AdoConn.m_pRecordset->MoveNext(); //将记录集指针移动到下一条记录
		num++;
	}

	double sum=0,sumj=0;
	for(int j=0;j<num;j++)
	{ 
		//使用SUM计算同种商品每一天销售的数量
		sql.Format(_T("select sum(sxsl) as ZSL from xs inner join sp on xs.spbh = sp.spbh \
				where sxsj = '%s' and xs.SPBH='%s'"),time.Format("%Y-%m-%d"),spbh[j]);
		m_AdoConn.m_pRecordset = m_AdoConn.GetRecordSet((_bstr_t)sql); //根据SQL语句查询
		if(!m_AdoConn.m_pRecordset->adoEOF) 
		{
			//取出当天每种商品的销售数量
			int zsl = m_AdoConn.m_pRecordset->GetCollect("ZSL"); 
			m_Grid.SetItemText(j,4,(_bstr_t)zsl);
			sum = sum + spdj[j] * zsl;//计算销售金额
			sumj = sumj + spjj[j] * zsl;
		}

	}
	sumj = sum - sumj;
	m_Money.Format(_T("%0.2f"),sum); //显示销售金额
	m_YMoney.Format(_T("%0.2f"),sumj); //显示盈利金额
	m_AdoConn.ExitConnect(); //关闭记录集并断开数据库连接
	UpdateData(FALSE); //更新控件显示数据
}
